import pandas as pd
import numpy as np
from matplotlib.pyplot import figure
import matplotlib.pyplot as plt
import seaborn as sns
#显示所有列
pd.set_option('display.max_columns', None)
#显示所有行
pd.set_option('display.max_rows', None)
#设置value的显示长度为100，默认为50
pd.set_option('max_colwidth',100)

#加载数据
train = pd.read_csv(r'数据集\train\train.csv')
stores = pd.read_csv(r"数据集\stores.csv")
oil = pd.read_csv(r"数据集\oil.csv")
transactions = pd.read_csv(r"数据集\transactions\transactions.csv")
holidays_events = pd.read_csv(r"数据集\holidays_events.csv")
test = pd.read_csv(r"数据集\test.csv")
#将数据特征进行整合
train = train.merge(stores, how="left", on='store_nbr')
train = train.merge(oil, how="left", on='date')
train = train.merge(transactions, how="left", on=['date','store_nbr'])
train = train.merge(holidays_events,on='date',how='left')
train = train.rename(columns={'type_x' : 'store_type','type_y':'holiday_type'})

test = test.merge(stores, how="left", on='store_nbr')
test = test.merge(oil, how="left", on='date')
test = test.merge(holidays_events,on='date',how='left')
test = test.rename(columns={'type_x' : 'store_type','type_y':'holiday_type'})
#处理缺失值
train.dropna(subset=['store_nbr'], axis=0, inplace=True)

def DrawLine(X_data, Y_data):
    fig = plt.figure()
    axes = fig.add_subplot(1, 1, 1)
    axes.plot(X_data, Y_data)
    plt.show()
#DrawLine(oil['date'], oil['dcoilwtico'])
train['dcoilwtico'] = train['dcoilwtico'].fillna(method='bfill')
test['dcoilwtico'] = test['dcoilwtico'].fillna(method='bfill')

train.transactions = train.transactions.replace(np.nan,0)

train[['locale','locale_name', 'description']] = train[['locale','locale_name', 'description']].replace(np.nan,'')
train['holiday_type'] = train['holiday_type'].replace(np.nan,'Work Day')
train['transferred'] = train['transferred'].replace(np.nan,False)

test[['locale','locale_name', 'description']] = test[['locale','locale_name', 'description']].replace(np.nan,'')
test['holiday_type'] = test['holiday_type'].replace(np.nan,'Work Day')
test['transferred'] = test['transferred'].replace(np.nan,False)

#print(train.tail())

train['date'] = pd.to_datetime(train['date'])
train['Quarter'] = train['date'].apply(lambda x: pd.to_datetime(x).quarter)
train['Week'] = train['date'].apply(lambda x: pd.to_datetime(x).week)
train['DayofWeek'] = train['date'].apply(lambda x: pd.to_datetime(x).dayofweek)
train['isWeekend'] = np.where(train['DayofWeek'].isin([5,6]),1,0)
train['Month'] = train['date'].apply(lambda x: pd.to_datetime(x).month)
train['Year'] = train['date'].apply(lambda x: pd.to_datetime(x).year)
train['Day'] = train['date'].apply(lambda x: pd.to_datetime(x).day)
train['season'] = train['Month'].apply(lambda x: 0 if x in [2,3] else 1 if x in [4,5,6] else 2 if x in [7,8] else 3 if x in [9,10,11] else 4)

print(train.head())

test['date'] = pd.to_datetime(train['date'])
test['Quarter'] = test['date'].apply(lambda x: pd.to_datetime(x).quarter)
test['Week'] = test['date'].apply(lambda x: pd.to_datetime(x).week)
test['DayofWeek'] = test['date'].apply(lambda x: pd.to_datetime(x).dayofweek)
test['isWeekend'] = np.where(test['DayofWeek'].isin([5,6]),1,0)
test['Month'] = test['date'].apply(lambda x: pd.to_datetime(x).month)
test['Year'] = test['date'].apply(lambda x: pd.to_datetime(x).year)
test['Day'] = test['date'].apply(lambda x: pd.to_datetime(x).day)
test['season'] = test['Month'].apply(lambda x: 0 if x in [2,3] else 1 if x in [4,5,6] else 2 if x in [7,8] else 3 if x in [9,10,11] else 4)
#print(test.head())

#可视化
train = train.reset_index()
plt.figure(figsize=(10, 5))
sns.lineplot(x='Month', y='sales', data=train)
plt.title("Monthly Sales")
plt.show()

plt.figure(figsize=(10, 5))
sns.lineplot(x='Year', y='sales', data=train)
plt.title("Sales by Year")
plt.show()

#产品系列销售分布
product_sales = train.groupby("family").sales.mean().sort_values(ascending = False).reset_index()
plt.figure(figsize=(10, 10))
sns.barplot(y='family', x='sales', data=product_sales)
plt.title("Product Family Sales", pad=15)
plt.tight_layout()
plt.show()

#stores类型分布情况
plt.figure(figsize=(20,10))
sns.barplot(x="store_type", y="sales", data=train)
plt.title('Store Type Distribution based on Sales')
plt.show()

#商店交易情况
store_transactions = train.groupby("store_nbr").transactions.mean().sort_values(ascending = False).reset_index()
store_transactions['store_nbr'] = store_transactions['store_nbr'].astype('category')
plt.figure(figsize=(10, 10))
sns.barplot(y='store_nbr', x='transactions', data=store_transactions, order=store_transactions.sort_values('transactions', ascending=False).store_nbr[0:10])
plt.title("Store by Transactions", pad=15)
plt.show()